package com.gxuwz.lhleavesys.dao.impl;


import com.gxuwz.lhleavesys.dao.CollegeDao;
import com.gxuwz.lhleavesys.db.DbUtil;
import com.gxuwz.lhleavesys.entity.College;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

@Repository
public class CollegeDaoImpl implements CollegeDao {

    @Autowired
    private DbUtil dbUtil;

    /**
     * 查询 BY ID
     * @param collegeID
     * @return
     */
    @Override
    public College findByCollegeID(String collegeID) {
        College college = null;
        String sql = "SELECT * FROM sys_department WHERE collegeID = ?";
        try (Connection conn = dbUtil.getConn();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, collegeID);
            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    college = new College();
                    college.setCollegeID(rs.getString("collegeID"));
                    college.setCollegeName(rs.getString("collegeName"));
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return college;
    }

    /**
     * 查询所有
     * @return
     */
    @Override
    public List<College> findAll() {
        List<College> colleges = new ArrayList<>();
        String sql = "SELECT * FROM sys_department";
        try (Connection conn = dbUtil.getConn();
             PreparedStatement ps = conn.prepareStatement(sql);
             ResultSet rs = ps.executeQuery()) {
            while (rs.next()) {
                College college = new College();
                college.setCollegeID(rs.getString("collegeID"));
                college.setCollegeName(rs.getString("collegeName"));
                colleges.add(college);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return colleges;
    }


    /**
     * 模糊和查询所有
     * @param searchTerm
     * @return
     */
    @Override
    public List<College> findAllorOne(String searchTerm) {
        List<College> colleges = new ArrayList<>();
        String sql;
        if (searchTerm == null || searchTerm.isEmpty()) {
            sql = "SELECT * FROM sys_department";
        } else {
            sql = "SELECT * FROM sys_department WHERE collegeName LIKE ?";
        }

        try (Connection conn = dbUtil.getConn();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            if (searchTerm != null && !searchTerm.isEmpty()) {
                ps.setString(1, "%" + searchTerm + "%");
            }
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    College college = new College();
                    college.setCollegeID(rs.getString("collegeID"));
                    college.setCollegeName(rs.getString("collegeName"));
                    colleges.add(college);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return colleges;
    }


    /**
     * 插入
     * @param college
     * @return
     */
    @Override
    public boolean insert(College college) {
        String sql = "INSERT INTO sys_department (collegeID, collegeName) VALUES (?, ?)";
        try (Connection conn = dbUtil.getConn();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, college.getCollegeID());
            ps.setString(2, college.getCollegeName());
            int rows = ps.executeUpdate();
            return rows > 0;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }

    /**
     * 更新
     * @param college
     * @return
     */
    @Override
    public boolean update(College college, String id) {
        String sql = "UPDATE sys_department SET collegeName = ? , collegeID = ? where collegeID=?";
        try (Connection conn = dbUtil.getConn();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, college.getCollegeName());
            ps.setString(2, college.getCollegeID());
            ps.setString(3, id);
            int rows = ps.executeUpdate();
            return rows > 0;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }

    /**
     * 删除 BY ID
     * @param collegeID
     * @return
     */
    @Override
    public boolean delete(String collegeID) {
        String sql = "DELETE FROM sys_department WHERE collegeID = ?";
        try (Connection conn = dbUtil.getConn();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, collegeID);
            int rows = ps.executeUpdate();
            return rows > 0;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }

    /**
     * 模糊查询 (未使用)
     * @param college
     * @return
     */
    @Override
    public List<College> BlurQuery(College college) {
        // List<College> collegeList = new ArrayList<College>();
        // String sql = "SELECT * from sys_department WHERE 1=1";
        // if (college.getCollegeName() != ""){
        //     sql +="and "+ college.getCollegeName();
        // }
        // try(Connection conn = dbUtil.getConn();
        // PreparedStatement ps = conn.prepareStatement(sql);
        // ResultSet rs = ps.executeQuery();){
        //     while (rs.next()){
        //         College collegeTemp = new College();
        //         collegeTemp.setCollegeID(rs.getString(1));
        //     }
        // }catch (Exception e){
        //     e.printStackTrace();
        // }
        return null;
    }
}
